package com.kehutong.wwin.service.util;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.coraframework.boot.Application;
import org.coraframework.config.Config;
import org.coraframework.converter.Converter;
import org.coraframework.util.Objects;

import java.io.*;
import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtil {


	/**
	 * 描述：获取IO流中的数据，组装成List<List<Object>>对象
	 *
	 * @param path filePath
	 * @return file content as list
	 * @throws IOException
	 * @throws FileNotFoundException
	 */
	public static List<String[]> readExcel(String path) throws Exception {
		Config config = Application.getConfig("web");
		File excelFile = new File(config.getString("webapp") + File.separator + path);

		if (!excelFile.exists()) {
			throw new NullPointerException(path);
		}

		try (FileInputStream input = new FileInputStream(excelFile)) {
			return readExcel(input, 7);
		}
	}

	public static List<String[]> readExcel(InputStream input, int columnNo) throws Exception {
		List<String[]> cellList = new ArrayList<>(32);

		Workbook workBook = WorkbookFactory.create(input);
		Sheet sheet = workBook.getSheetAt(0);

		// 遍历当前sheet中的所有行
		Row row;
		int lastRowNum = sheet.getLastRowNum();
		if (Objects.isNull(sheet.getRow(lastRowNum).getCell(0))) {
			lastRowNum --;
		}

		for (int i = 1; i <= lastRowNum ; i ++) {
			if (Objects.isNull(row = sheet.getRow(i))) {
				continue;
			}

			String[] celValue = new String[columnNo + 1];
			for (int j = 0; j <= columnNo; j++) {
				celValue[j] = getCellValue(row.getCell(j));
			}
			cellList.add(celValue);
		}

		return cellList;
	}


	/**
	 * 描述：获取IO流中的数据，组装成List<List<Object>>对象
	 *
	 * @param name fileName
	 * @return write excel file
	 * @throws IOException IOexception
	 */
	public static File writeXlsFile(String name, String[] titles, List<String[]> contexts)
			throws IOException {
		File file = new File(name);
		try (HSSFWorkbook wb = new HSSFWorkbook()) {
			HSSFSheet sheet = wb.createSheet("sheet1");
			HSSFRow row = sheet.createRow(0);
			HSSFCellStyle style = wb.createCellStyle();
			style.setAlignment(HorizontalAlignment.CENTER);
			sheet.setDefaultColumnWidth(20);
			sheet.setDefaultRowHeightInPoints(20);

			for (int i = 0; i < titles.length; i++) {
				row.createCell(i).setCellValue(titles[i]);
			}
			HSSFRow rows;
			for (int i = 0; i < contexts.size(); i++) {
				rows = sheet.createRow( i + 1);
				String[] content = contexts.get(i);
				for (int j = 0; j < content.length; j++) {
					rows.createCell(j).setCellValue(content[j]);
				}
			}
			wb.write(file);
		}
		return file;
	}
	
    /**
     * workbook转临时文件
     * IO流处理
     */
    public static File writeXlsFile(Workbook wb, String name, String suffix) throws IOException {
        FileOutputStream fos = null;
        File tempFile;
        try {
            tempFile = File.createTempFile(name, suffix);
            fos = new FileOutputStream(tempFile);
            wb.write(fos);
        } catch (IOException e) {
            e.printStackTrace();
            throw e;
        } finally {
            try {
                if (fos != null) {
                    fos.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return tempFile;
    }

	public static String getCellValue(Cell cell) {
		if (Objects.isNull(cell)) {
			return null;
		}


		cell.setCellType(CellType.STRING);
		String value = cell.getStringCellValue();
		if (value != null) {
			value = value.trim();
		}
		return value;
	}
	
    public static void autoSizeColumn(Sheet sheet, int i) {
        if (sheet == null) {
            return;
        }
        // 调整每一列宽度
        sheet.autoSizeColumn(i);
        // 解决自动设置列宽中文失效的问题
        int width = sheet.getColumnWidth(i) * 17 / 10;
        // 避免宽度过长导致抛异常
        if (width > 255 * 256) {
            width = 255 * 10;
        }
        sheet.setColumnWidth(i, width);
    }
    
    public static LocalDate getLocalDateFromCell(Row row, int index) {
        Cell cell = row.getCell(index);
        if (cell == null) {
            return null;
        }
        try {
            return Converter.toLocalDate(cell.getDateCellValue());
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            return Converter.toLocalDate(cell.getStringCellValue());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static BigDecimal getBigDecimalFromCell(Row row, int index) {
        Cell cell = row.getCell(index);
        if (cell == null) {
            return null;
        }
        if (cell.getCellTypeEnum() == CellType.NUMERIC) {
            try {
            	cell.setCellType(CellType.STRING);
                String d = cell.getStringCellValue();
                return new BigDecimal(d).setScale(2);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        try {
            return new BigDecimal(cell.getStringCellValue()).setScale(2);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static BigDecimal getBigDecimalFromCell(String cell) {
        if (cell == null) {
            return null;
        }

        try {
            return new BigDecimal(cell).setScale(2);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static XSSFColor getColorIndex(int r, int g, int b) {
        return new XSSFColor(new java.awt.Color(r, g, b));
    }

	public static void main(String[] args) throws Exception {
		readExcel("Documents/花郡.xlsx");
	}

}
